/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fresher.utility;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author quangtm5
 */
public class ConnectionObject 
{
    
    private String USER_NAME = "";
    private String PASS_WORD = "";
    private String DATABASE_NAME = "";
    boolean free = Boolean.FALSE;
    
    //Database connection, statement 
    private Connection connection = null;
    private Statement statement = null;
   
    
    public ConnectionObject(String USER_NAME, String PASS_WORD, String DATABASE_NAME)
    {
        this.USER_NAME = USER_NAME;
        this.PASS_WORD = PASS_WORD;
        this.DATABASE_NAME = DATABASE_NAME;
        
       // create connection and statement with default value
       try 
       {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager
                    .getConnection("jdbc:mysql://localhost:3306/" + this.DATABASE_NAME, this.USER_NAME, this.PASS_WORD);

            statement = connection.createStatement();
       } 
       catch (ClassNotFoundException | SQLException exception) 
       {
       }      
        
        //connection is free now
        this.free = true;
        
    }
    
    /**
     * Check if the Connection is free
     * 
     * @return boolean if free return true
     */
    public boolean isFree()
    {
        return this.free;
    }
    
    /**
     * Set connection busy now
     */
    public void setBusy()
    {
        this.free = Boolean.FALSE;
    }
    
    /**
     * Set connection free now
     */
    public void setFree()
    {
        this.free = Boolean.TRUE;
    }
    
    /**
     * Get DatabaseUtility object
     * 
     * @return DatabaseUtility object
     */
    public ConnectionObject getConnectionObject()
    {
        try
        {
            //check if the connection is closed because of timeout
            if(this.connection.isClosed())
            {
                //reconnecto to server using a new connection
                this.connection = DriverManager
                    .getConnection("jdbc:mysql://localhost:3306/" + this.DATABASE_NAME, this.USER_NAME, this.PASS_WORD);
                
                this.statement = connection.createStatement();
//                System.out.println("Reconnect to server because of timeout.");
            }
            
            
        }
        catch (SQLException ex)        
              
        {
            Logger.getLogger(ConnectionObject.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return this;
    }
    
    /**
     * Execute sql query statement, not that this function not suitable for
     * INSERT and DELETE statement
     *
     * @param sql sql query statement
     * @return ResultSet of query statement
     * @throws SQLException
     */
    public ResultSet query(String sql) throws SQLException 
    {
        return statement.executeQuery(sql);
    }

    /**
     * Execute sql update statement, which include INSERT, UPDATE, or DELETE
     * statement
     *
     * @param sql sql string to execute
     * @return number of effected row
     * @throws SQLException
     */
    public int executeUpdate(String sql) throws SQLException 
    {
        return statement.executeUpdate(sql);
    }
}
